package com.sgcc.manager.sql.excelUtil;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


/**
 * <br>Title:POI导出excel文件的操作类
 * <br>Description:POI导出excel文件
 * <br>Author:王腾龙
 * <br>Date:Mar 20, 2015
 */ 
public class Xls2Excel {
	
	/**
	 * 导出excel的测试Demo
	 * @param out 数据流
	 * @throws Exception
	 */
    public static void xlsDto2Excel(OutputStream out) throws Exception {
        // 获取总列数
        int CountColumnNum = 5;
        // 创建Excel文档
        HSSFWorkbook hwb = new HSSFWorkbook();
        // sheet 对应一个工作页
        HSSFSheet sheet = hwb.createSheet("pldrxkxxmb");
        HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始
        HSSFCell[] firstcell = new HSSFCell[CountColumnNum];
        String[] names = new String[CountColumnNum];
        names[0] = "学号";
        names[1] = "姓名";
        names[2] = "学院";
        names[3] = "课程名";
        names[4] = "成绩";
        //创建标题栏
        for (int j = 0; j < CountColumnNum; j++) {
            firstcell[j] = firstrow.createCell(j);
            firstcell[j].setCellValue(new HSSFRichTextString(names[j]));
        }
        //添加数据
        for (int i = 0; i < 2; i++) {
            // 创建一行
            HSSFRow row = sheet.createRow(i + 1);
            // 得到要插入的每一条记录
            for (int colu = 0; colu <= 4; colu++) {
                // 在一行内循环
                HSSFCell xh = row.createCell(0);
                xh.setCellValue("学生1");
                HSSFCell xm = row.createCell(1);
                xm.setCellValue("姓名1");
                HSSFCell yxsmc = row.createCell(2);
                yxsmc.setCellValue("学院1");
                HSSFCell kcm = row.createCell(3);
                kcm.setCellValue("课程1");
                HSSFCell cj = row.createCell(4);
                cj.setCellValue("成级1");
            }
        }
        hwb.write(out);
        System.out.println("数据库导出成功");
    }
    
    /**
     * POI控件导出数据到excel文件
     * @param OutputStream out 数据输出流
     * @param Map dataMap 存储的数据
     * @param String 工作页名称
     * @param String fieldList 数据中的头标题标识符
     * @param String valueList 数据中的数据标识符
     * @throws Exception 在导入Excel的过程中抛出异常
     */
    public static void Map2Excel(OutputStream out,Map dataMap,String sheetName,String fieldList,String valueList) throws Exception {
    	//导出excel
		List<Map<String,String>> tableFieldList=new ArrayList<Map<String,String>>();
		//数据
		List<Map<String, Object>> dataList=new ArrayList<Map<String,Object>>();
		//获取字段列表
		tableFieldList=(List<Map<String, String>>) dataMap.get(fieldList);
		//获取数据列表
		dataList=(List<Map<String, Object>>) dataMap.get(valueList);
		// 创建Excel文档
        HSSFWorkbook hwb = new HSSFWorkbook();
        // sheet 对应一个工作页
        HSSFSheet sheet=hwb.createSheet(sheetName);
        //创建标题行
        HSSFRow headrow=sheet.createRow(0);// 下标为0的行开始
        //创建标题列
        HSSFCell[] firstcell = new HSSFCell[tableFieldList.size()];  //根据标题数据的长度创建列
        String[] tempHeadStr= new String[tableFieldList.size()];
        //添加标题数据
        for (int i = 0; i < tableFieldList.size(); i++) {
        	Map<String, String> map=tableFieldList.get(i);
			String nameStr=map.get("name");
			tempHeadStr[i]=map.get("name");
        	firstcell[i]=headrow.createCell(i);
        	firstcell[i].setCellValue(new HSSFRichTextString(nameStr));
		}
        //添加数据
        for (int i = 0; i < dataList.size(); i++) {
			HSSFRow dataRow=sheet.createRow(i+1);
			//值列表
			Map<String, Object> map=dataList.get(i);
			HSSFCell cell=null;
			for (int colu = 0; colu < tempHeadStr.length; colu++) {
				cell=dataRow.createCell(colu);
				cell.setCellValue(String.valueOf(map.get(tempHeadStr[colu])==null ? "" : map.get(tempHeadStr[colu])));
			}
		}
        hwb.write(out);
        System.out.println("通过HSSF控件导出excel完成。");
    }
    
    public static void main(String[] args) {
    	// 创建文件输出流，准备输出电子表格
		try {
			File file=new File("pldrxkxxmb.xls");
			if (!file.exists()) {
				file.createNewFile();
			}
			OutputStream out = new FileOutputStream(file);
			xlsDto2Excel(out);
	        out.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
 
}
